<?php
	class AttendanceController
	{
		public function attendanceImportHandler($year, $month)
		{
			$odbc_connect = odbc_connect("fingerprint", "" , "");
			$mysql_connect = mysql_connect('localhost', 'root', '$$Cr34+co@@');
			if (($odbc_connect) && ($mysql_connect))
			{		
				//Get the last employee_id of OrangeHrm
				mysql_select_db('hr_mysql', $mysql_connect);
				$mysql_query = "select emp_number, employee_id from hs_hr_employee order by emp_number desc";
				$mysql_result = mysql_query($mysql_query);
				if(!$mysql_result)
				{
					exit;
				}
				$mysql_row = mysql_fetch_row($mysql_result);		
				$max_emp_number = $mysql_row[0];
				$max_employee_id = $mysql_row[1];
				
				//Get all employee from FingerPrint		
				$odbc_query = "select * from USERINFO";
				$odbc_row = odbc_exec($odbc_connect, $odbc_query);
				
				$emp_number = $max_emp_number;
				$employee_id = $max_employee_id;
				while(odbc_fetch_row($odbc_row))
				{			
					//Employee fetching
					$employee_name = odbc_result($odbc_row, 4);			
					$mysql_query = "select * from hs_hr_employee where emp_firstname = '$employee_name'";			
					$mysql_result = mysql_query($mysql_query);
					$num_rows = mysql_num_rows($mysql_result);			
					
					//If fetched employee does not exist
					if($num_rows == 0)
					{
						//Increment by 1
						$emp_number += 1;
						
						$employee_id += 1;
						$employee_id = "0000" . $employee_id;	
						$employee_id = substr($employee_id, 4*-1);				
						
						//Add new employee to OrangeHrm
						$mysql_query = "insert into hs_hr_employee (emp_number, employee_id, emp_lastname, emp_firstname) values ('$emp_number', '$employee_id', '', '$employee_name')";				
						mysql_query($mysql_query);
					}			
					
					mysql_free_result($mysql_result);
				}
				
				//end of employee import process
				echo 'Import employee: Done<br/>';
				
				//Get the attendance records					
				$odbc_query = "select * from CHECKINOUT WHERE YEAR(CHECKTIME) = '$year' AND MONTH(CHECKTIME) = '$month'";
				$odbc_row = odbc_exec($odbc_connect, $odbc_query);
				while(odbc_fetch_row($odbc_row))
				{
					//Attendance fetching
					$employee_id = odbc_result($odbc_row, 1);
					$check_time = odbc_result($odbc_row, 2);
					$check_type = odbc_result($odbc_row, 3);
					$mysql_query = "select * from hs_hr_attendance_fingerprint where employee_id = '$employee_id' and check_time = '$check_time' and check_type = '$check_type'";			
					$mysql_result = mysql_query($mysql_query);
					$num_rows = mysql_num_rows($mysql_result);
					
					if($num_rows == 0)
					{
						//Add attendance record to OrangeHrm
						$mysql_query = "insert into hs_hr_attendance_fingerprint (employee_id, check_time, check_type) values ('$employee_id', '$check_time', '$check_type')";				
						mysql_query($mysql_query);
					}					
				}
				
				//end of attendance import process
				echo 'Import attendance: Done<br/>';
				
				//Pre-calculate attendance		
				$mysql_query = "delete from hs_hr_attendance_precalculate where year(check_date) = '$year' and month(check_date) = '$month'";
				mysql_query($mysql_query);
				$mysql_query = "delete from hs_hr_attendance where year(punchin_time) = '$year' and month(punchin_time) = '$month'";
				mysql_query($mysql_query);		
				
				$mysql_query = "INSERT INTO `hs_hr_attendance_precalculate` (`employee_id`, `morning_in`, `check_date`) SELECT employee_id, `morning_in_time`, `check_date` FROM `v_attendance_morning_in` WHERE year(check_date) = '$year' and month(check_date) = '$month'";
				mysql_query($mysql_query);
				
				$mysql_query = "INSERT INTO `hs_hr_attendance_precalculate` (`employee_id`, `morning_out`, `check_date`) SELECT employee_id, `morning_out_time`, `check_date` FROM `v_attendance_morning_out` WHERE year(check_date) = '$year' and month(check_date) = '$month'";
				mysql_query($mysql_query);

				$mysql_query = "INSERT INTO `hs_hr_attendance_precalculate` (`employee_id`, `afternoon_in`, `check_date`) SELECT employee_id, `afternoon_in_time`, `check_date` FROM `v_attendance_afternoon_in` WHERE year(check_date) = '$year' and month(check_date) = '$month'";
				mysql_query($mysql_query);

				$mysql_query = "INSERT INTO `hs_hr_attendance_precalculate` (`employee_id`, `afternoon_out`, `check_date`) SELECT employee_id, `afternoon_out_time`, `check_date` FROM `v_attendance_afternoon_out` WHERE year(check_date) = '$year' and month(check_date) = '$month'";
				mysql_query($mysql_query);
				echo 'Pre-calculate attendance: Done<br/>';

				//Max attendance 		
				$mysql_query = "select attendance_id from hs_hr_attendance order by attendance_id desc limit 1";		
				$mysql_result = mysql_query($mysql_query);
				if(!$mysql_result)
					exit;
				$mysql_row = mysql_fetch_row($mysql_result);		
				$max_attendance_id = $mysql_row[0];		
				
				$mysql_query = 
					"SELECT 
						employee_id,
						MIN(morning_in) AS morning_in,
						MIN(morning_out) AS morning_out,
						MIN(afternoon_in) AS afternoon_in,
						MIN(afternoon_out) AS afternoon_out
					FROM 
						`hs_hr_attendance_precalculate`
					GROUP BY
						employee_id,
						check_date
					ORDER BY 
						employee_id,
						check_date";
				$mysql_result = mysql_query($mysql_query);
				if(!$mysql_result)
					exit;
				
				$attendance_id = $max_attendance_id;
				while($mysql_row = mysql_fetch_array($mysql_result)) 
				{
					$employee_id = $mysql_row[0];
					
					$morning_in = strlen($mysql_row[1]) != 0 ? "'" . mysql_escape_string($mysql_row[1]) . "'" : "null";
					$morning_out = strlen($mysql_row[2]) != 0 ? "'" . mysql_escape_string($mysql_row[2]) . "'" : "null";
					$afternoon_in = strlen($mysql_row[3]) != 0 ? "'" . mysql_escape_string($mysql_row[3]) . "'" : "null";
					$afternoon_out = strlen($mysql_row[4]) != 0 ? "'" . mysql_escape_string($mysql_row[4]) . "'" : "null";
					//$timestamp_diff = 0;
			
					//$mysql_query = "select * from hs_hr_attendance where employee_id = '$employee_id' and punchin_time = '$punchin_time' and punchout_time = '$punchout_time'";
					//$attendance_result = mysql_query($mysql_query);
					//$num_rows = mysql_num_rows($attendance_result);
					
					//if($num_rows == 0)
					//{
						$attendance_id += 1;
						//Add new attendance record to OrangeHrm
						
						$mysql_query = 
							"insert into hs_hr_attendance (attendance_id, employee_id, punchin_time, punchout_time, timestamp_diff, status, in_note, out_note) values ('$attendance_id', ". 
							"'$employee_id', ". 
							"$morning_in, " . 
							"$afternoon_out, " . 
							" 0, " . 
							"'1', " . 
							"$morning_out, " . 
							"$afternoon_in)";				
						mysql_query($mysql_query);
					//}			
				}	

				//end of attendance calculation process
				echo 'Calculate attendance: Done<br/>';		
			}
			
			mysql_close($mysql_connect);
			odbc_close($odbc_connect);
		}
	}		
?>